package test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Properties;

public class DBConn {

	Connection con = null;
	Statement stm = null;
	String sql = "";

	public DBConn() { // 构造方法进行初始化

	}

	public void creatConnectInstance() {
		try {
			Properties prop = new Properties();
			prop.put("charSet", "gbk"); // 解决中文乱码
			Class.forName("org.postgresql.Driver").newInstance();
			String url = "jdbc:postgresql://localhost:5432/weibo2015";// 更改数据库链接

			con = DriverManager.getConnection(url, "postgres", "11225566");// 更改数据库链接的账户和密码
			// 对于查询数据的不敏感，就是在查询过程中数据变了，也查询结果不变。和可以更新数据。
			stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	public void destroyConnectInstance() {
		try {
			con.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	/**
	 * 插入用户
	 * 
	 * @param uID
	 * @param screenName
	 * @param uName
	 * @param province
	 * @param city
	 * @param location
	 * @param decription
	 * @param userDomain
	 * @param gender
	 * @param follows
	 * @param friends
	 * @param statuses
	 * @param favourites
	 * @param createAt
	 * @param geoEnabled
	 */
	public void insertUser(String uID, String screenName, String uName,
			int province, int city, String location, String decription,
			String userDomain, String gender, int follows, int friends,
			int statuses, int favourites, String createAt, boolean geoEnabled) { // 插入数据
		this.creatConnectInstance();
		sql = "insert into UserInfo(UID,ScreenName,UName,Province,City,Locations,Description,UserDomain, Gender, FollowersCount,"
				+ "FriendsCount, StatusesCount, FavouritesCount, CreateAt, GeoEnabled)"
				+ "values('"
				+ uID
				+ "','"
				+ screenName
				+ "','"
				+ uName
				+ "',"
				+ province
				+ ","
				+ city
				+ ",'"
				+ location
				+ "','"
				+ decription
				+ "','"
				+ userDomain
				+ "','"
				+ gender
				+ "','"
				+ follows
				+ "','"
				+ friends
				+ "','"
				+ statuses
				+ "','"
				+ favourites
				+ "','"
				+ createAt + "'," + geoEnabled + ");";
		try {
			int i = stm.executeUpdate(sql);
			// System.out.println("i="+i);;
			if (i > 0) { // i的值表示语句操作的记录数量，i=0表示未进行数据库操作
				System.out.println("添加成功UID:  " + uID);
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("添加失败UID: " + uID + " - " + e.getMessage()); //
		}
		this.destroyConnectInstance();
	}

	/**
	 * 用户的所发微博内容
	 * 
	 * @param uID
	 * @param createAt
	 * @param sId
	 * @param mId
	 * @param gridId
	 * @param text
	 * @param source
	 * @param poiid
	 * @param geo
	 * @param longitude
	 * @param latitude
	 * @param reCount
	 * @param comCount
	 * @param annotation
	 */
	public void insertStatusPoi(String uID, String createAt, String sId,
			String mId, int gridId, String text, String source, String poiid,
			String geo, double longitude, double latitude, int reCount,
			int comCount, String annotation) { // 插入数据
		sql = "insert into StatusInfo(UID,CreateAt,SID,MID,IdStr,Texts,Source,OriginalPic,Geo,Latitude,Longitude,RepostsCount,CommentsCount,Annotations)"
				+ "values('"
				+ uID
				+ "','"
				+ createAt
				+ "','"
				+ sId
				+ "','"
				+ mId
				+ "','"
				+ gridId
				+ "','"
				+ text
				+ "','"
				+ source
				+ "','"
				+ poiid
				+ "','"
				+ geo
				+ "',"
				+ longitude
				+ ","
				+ latitude
				+ ","
				+ reCount + "," + comCount + ",'" + annotation + "');";
		this.creatConnectInstance();
		try {
			int i = stm.executeUpdate(sql);
			// System.out.println("i="+i);;
			if (i > 0) { // i的值表示语句操作的记录数量，i=0表示未进行数据库操作
				System.out.println("添加成功SID: " + sId);
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			// System.out.println("添加失败SID: "+ sId+ " - " + e.getMessage()); //
		}
		this.destroyConnectInstance();
	}

	public ResultSet query(String sql) { // 查询数据
		this.creatConnectInstance();
		ResultSet rs = null;
		try {
			rs = stm.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 查询微博内容数据
	 * 
	 * @param sql
	 * @return
	 */
	public String queryTexts(String sql) {
		String strUid = null;
		this.creatConnectInstance();
		ResultSet rs = null;
		try {
			rs = stm.executeQuery(sql);
			if (rs.next()) {
				strUid = rs.getString("Texts");
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();
		return strUid;
	}

	/**
	 * 查询sql中UID用户ID数据
	 * 
	 * @param sql
	 * @return
	 */
	public String queryUID(String sql) {
		String strUid = null;
		this.creatConnectInstance();
		ResultSet rs = null;
		try {
			rs = stm.executeQuery(sql);
			if (rs.next()) {
				strUid = rs.getString("UID");
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();
		return strUid;
	}

	/**
	 * 根据uid查询所有数据
	 * 
	 * @param id
	 * @return
	 */
	public String queryUserId(String id) {
		String strUid = null;
		sql = "select * from userinfo where uid='" + id + "';";
		this.creatConnectInstance();
		ResultSet rs = null;
		try {
			rs = stm.executeQuery(sql);
			if (rs.next()) {
				strUid = rs.getString("UID");
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();
		return strUid;
	}

	/**
	 * 查询一定区域的uid
	 * 
	 * @param start
	 * @param end
	 * @return
	 */
	public ResultSet queryUID(long start, long end) {
		sql = "select UID from userinfo where id > " + start + " and id<="
				+ end + " order by id;";
		this.creatConnectInstance();
		ResultSet rs = null;
		try {
			rs = stm.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();
		return rs;
	}

	/**
	 * 插入poi数据
	 * 
	 * @param poiid
	 * @param title
	 * @param address
	 * @param lon
	 * @param lat
	 * @param category3
	 * @param city
	 * @param province
	 * @param country
	 * @param categorys
	 * @param categoryName
	 * @param checkinNum
	 * @param checkinUserNum
	 * @param tipNum
	 * @param photoNum
	 * @param poiStreetAdds
	 */
	public void insertPois(String poiid, String title, String address,
			double lon, double lat, String category3, String city,
			String province, String country, String categorys,
			String categoryName, long checkinNum, long checkinUserNum,
			long tipNum, long photoNum, String poiStreetAdds) { // long
																// weiboid,
		sql = "insert into PoiCategory(Poiid,Title,Address,CityId,Province,Country, Longitude,Latitude,Category3," // Phone,Postcode,
				+ "Category,Catenames,CheckinNum,CheckinUserNum,PhotoNum,TipNum,poiStreetAdds)"
				+ "values('"
				+ poiid
				+ "','"
				+ title
				+ "','"
				+ address
				+ "','"
				+ city
				+ "','"
				+ province
				+ "','"
				+ country
				+ "',"
				+ lon
				+ ","
				+ lat
				+ ",'"
				+ category3
				+ "','"
				+ categorys
				+ "','"
				+ categoryName
				+ "',"
				+ checkinNum
				+ ","
				+ checkinUserNum
				+ ","
				+ photoNum
				+ "," + tipNum + ",'" + poiStreetAdds + "');";
		this.creatConnectInstance();
		try {
			int i = stm.executeUpdate(sql);
			// System.out.println("i="+i);;
			if (i > 0) { // i的值表示语句操作的记录数量，i=0表示未进行数据库操作
				System.out.println("添加成功POI: " + poiid);
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			// System.out.println("添加失败POI: "+ poiid); // + " - " +
			// e.getMessage()
		}
		this.destroyConnectInstance();
	}

	/**
	 * 插入用户签到
	 * 
	 * @param uid
	 * @param poiid
	 * @param createAt
	 */
	public void insertUserCkins(String uid, String poiid, String createAt) {
		sql = "insert into usercheckins(uid,poiid,checkintime) values('" + uid
				+ "','" + poiid + "','" + createAt + "');";
		this.creatConnectInstance();
		try {
			int i = stm.executeUpdate(sql);
			// System.out.println("i="+i);;
			if (i > 0) { // i的值表示语句操作的记录数量，i=0表示未进行数据库操作
				System.out.println("添加成功Checkins: " + uid);
			}
		} catch (SQLException e) {
			// System.out.println(e.getMessage());
			// System.out.println("添加失败Checkins: "+ uid+ " - " +
			// e.getMessage()); //
		}
		this.destroyConnectInstance();
	}

	/**
	 * 查询poi数据
	 * 
	 * @param id
	 * @return
	 */
	public String queryPoiId(String id) {
		String strPoi = null;
		sql = "select * from Poicategory where Poiid='" + id + "';";
		this.creatConnectInstance();
		ResultSet rs = null;
		try {
			rs = stm.executeQuery(sql);
			if (rs.next()) {
				strPoi = rs.getString("Poiid");
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();
		return strPoi;
	}

	public void close() {// 关闭数据库联接
		try {
			// if (rs!=null) rs.close();
			if (stm != null)
				stm.close();
			if (con != null)
				con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 根据id更新签到表中的时间
	 * 
	 * @param id
	 * @param year
	 * @param month
	 * @param day
	 * @param hour
	 * @param week
	 */
	public void updateCheckinTimebyID(long id, int year, int month, int day,
			int hour, int week) {
		sql = "update usercheckins set ckyear = " + year + ",ckmonth=" + month
				+ ", ckday=" + day + ", ckhour=" + hour + ",ckweek=" + week
				+ " where id = " + id + ";";
		try {
			Statement stm2 = con.createStatement();
			int i = stm2.executeUpdate(sql);
			// System.out.println("i="+i);;
			// if(i > 0) { //i的值表示语句操作的记录数量，i=0表示未进行数据库操作
			// System.out.println("添加成功id: "+ id);
			// }
		} catch (SQLException e) {
			// e.printStackTrace();
			// System.out.println("添加失败id: "+ id);
			// try {
			// Thread.sleep(10000);
			// } catch (InterruptedException e1) {
			// e1.printStackTrace();
			// }

		}
	}

	/**
	 * 提取签到时间表中的数据
	 * 
	 * @param id
	 * @return
	 */
	private int[] getYMDH(long id) {
		int[] ymdh = new int[5];
		String[] ymd = { "year", "month", "day", "hour", "DOW" };// DOW每周的星期号(0-6；星期天是0)
																	// (仅用于timestamp)
		try {
			Statement stm2 = con.createStatement();
			ResultSet rs2 = null;
			for (int i = 0; i < 5; i++) {
				// sql extract提取时间
				sql = "select extract(" + ymd[i]
						+ " from checkintime) from usercheckins where id=" + id
						+ ";";
				rs2 = stm2.executeQuery(sql);
				if (rs2.next()) {
					ymdh[i] = rs2.getInt(1);
				}
				// rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ymdh;
	}

	/**
	 * 获取签到表的时间
	 * 
	 * @param id
	 * @return
	 */

	private Time getTime(long id) {
		Time ti = null;
		try {
			Statement stm2 = con.createStatement();
			ResultSet rs2 = null;
			sql = "select extract(time from checkintime) from usercheckins where id="
					+ id + ";";
			rs2 = stm2.executeQuery(sql);
			if (rs2.next()) {
				ti = rs2.getTime(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ti;
	}

	/**
	 * 更新max id的签到的时间
	 */
	public void updateCheckinTime() {
		this.creatConnectInstance();
		ResultSet rs = null;
		sql = "select max(id) from usercheckins;";
		long id = 0;
		int[] ymdh = new int[5];
		// Time ti=null;
		try {
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt(1);
				ymdh = getYMDH(id);
				// ti=getTime(id);
				updateCheckinTimebyID(id, ymdh[0], ymdh[1], ymdh[2], ymdh[3],
						ymdh[4]);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();

	}

	/**
	 * 根据网格Id获取经纬度 Grids
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */

	public double[] stationPosition(int id) throws SQLException {

		double[] sPos = new double[] { 0.0, 0.0 };
		sql = "select Longitude,Latitude from Grids where id = " + id + ";";
		ResultSet rs = this.query(sql);
		if (rs != null) {
			rs.next();
			sPos[0] = sPos[0] + rs.getDouble("Longitude");
			sPos[1] = sPos[1] + rs.getDouble("Latitude");
		}
		rs.close();
		con.close();
		return sPos;
	}

	/**
	 * 根据poiid获取大类1和小类2 v_PoiCategory
	 * 
	 * @param pid
	 * @return
	 */
	public int[] PoiCategory(String pid) {
		int[] pCate = new int[2];
		sql = "select Category1,Category2 from v_PoiCategory where poiid='"
				+ pid + "';";
		ResultSet rs = this.query(sql);
		try {
			if (rs.next()) {
				// rs.last(); pCate[0] = rs.getRow(); rs.first();
				pCate[0] = rs.getInt(1);
				pCate[1] = rs.getInt(2);
			} else {
				pCate = null;
			}
			rs.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pCate;
	}

	/**
	 * 根据破iid获取类名和title
	 * 
	 * @param pid
	 * @return
	 */

	public String PoiNames(String pid) {

		String sPos = "";
		sql = "select catenames,title from v_PoiCategory where Poiid='" + pid
				+ "';";
		ResultSet rs = this.query(sql);
		try {
			if (rs != null) {
				rs.next();
				sPos = rs.getString(1) + "-" + rs.getString(2);
			}
			rs.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sPos;
	}

	/**
	 * 根据所给的poiTfIdf3的poi点集和相应的类别号来获取该类下点集合的投影坐标（单位：KM）
	 * 
	 * @param poiTfIdf3
	 * @param level
	 * @param cate
	 * @return
	 */
	public double[][] PoiCoordsAtCate(HashMap<String, Double> poiTfIdf3,
			int level, int cate) {
		double[][] coord;
		String poiids = "";
		for (String pid : poiTfIdf3.keySet()) {
			poiids = poiids + "'" + pid + "',";
		}
		poiids = poiids.substring(0, poiids.length() - 1);
		String category = "";
		if (level == 1)
			category = "Category1";
		if (level == 2)
			category = "Category2";
		String sql1 = "select Point_X,Point_Y from v_PoiCategory where "
				+ category + "=" + cate + "and poiid in (" + poiids + ");";
		ResultSet rs1 = this.query(sql1);
		try {
			rs1.last();
			int len1 = rs1.getRow();
			rs1.first();
			if (len1 > 0) {
				coord = new double[len1][2];
				for (int i = 0; i < len1; i++) {
					coord[i][0] = rs1.getDouble(1) / 1000;
					coord[i][1] = rs1.getDouble(2) / 1000;
					rs1.next();
				}
				rs1.close();
				con.close();
				return coord;
			} else {
				rs1.close();
				con.close();
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// public static void main(String[] args) {
	// DBConn d = new DBConn();
	// d.updateCheckinTime(4744901, 6044902);
	// File filename = new
	// File("C://Java//workspace//weibo4j-oauth1//AddPoiId.txt");
	// InputStreamReader reader = null;
	// try {
	// reader = new InputStreamReader(new FileInputStream(filename),"UTF-8");
	//
	// @SuppressWarnings("resource")
	// BufferedReader br = new BufferedReader(reader);
	// String poiid = br.readLine();
	// while(poiid!=null){
	// String[] idpoi = poiid.split("\t");
	// d.updatePoiid(Integer.parseInt(idpoi[0]),idpoi[1]);
	// poiid=br.readLine();
	// }
	// } catch (IOException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	// d.close();
	// }

	public void insertTimeLine(String uid, String poiid, int commentsCount,
			String geo, double latitude, double longitude, String text,
			String sourceName, String createAt) {
		// sql =
		// "insert into usertimeline(uid,poiid,commentsCount, geo,latitude,longitude,text,sourceName,checkintime) values"
		// + "('" + uid + "','" + poiid + "','"
		// + ""+ commentsCount + "','"
		// + ""+ geo + "','"
		// + ""+ latitude + "','"
		// + ""+ longitude + "','"
		// + ""+ text + "','"
		// + ""+ sourceName + "','"
		//
		// + createAt
		// + "');";
		this.creatConnectInstance();
		sql = "insert into usertimeline(uid,poiid,commentsCount, geo,latitude,longitude,text,sourceName,checkintime) values"
				+ "(?,?,?,?,?,?,?,?,?)";
		try {

			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, uid);
			ps.setString(2, poiid);
			ps.setInt(3, commentsCount);
			ps.setString(4, geo);
			ps.setDouble(5, latitude);
			ps.setDouble(6, longitude);
			ps.setString(7, text);
			ps.setString(8, sourceName);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
			try {
				ps.setTimestamp(9, new java.sql.Timestamp((sdf.parse(createAt)).getTime()));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
			}  
			

			ps.executeUpdate();
			System.out.println("添加成功timeline: " + uid);
		} catch (SQLException e1) {
//			System.out.println(e1);
			// TODO Auto-generated catch block
//			e1.printStackTrace();
		}
		this.destroyConnectInstance();
	}

	public void updateTimelineTime() {
		this.creatConnectInstance();
		ResultSet rs = null;
		sql = "select max(id) from usertimeline;";
		long id = 0;
		int[] ymdh = new int[5];
		// Time ti=null;
		try {
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt(1);
				ymdh = getYMDH2(id);
				// ti=getTime(id);
				updatetimelineTimebyID(id, ymdh[0], ymdh[1], ymdh[2], ymdh[3],
						ymdh[4]);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.destroyConnectInstance();

	}

	public void updatetimelineTimebyID(long id, int year, int month, int day,
			int hour, int week) {
		sql = "update usertimeline set ckyear = " + year + ",ckmonth=" + month
				+ ", ckday=" + day + ", ckhour=" + hour + ",ckweek=" + week
				+ " where id = " + id + ";";
		try {
			Statement stm2 = con.createStatement();
			int i = stm2.executeUpdate(sql);
			// System.out.println("i="+i);;
			// if(i > 0) { //i的值表示语句操作的记录数量，i=0表示未进行数据库操作
			// System.out.println("添加成功id: "+ id);
			// }
		} catch (SQLException e) {
			// e.printStackTrace();
			// System.out.println("添加失败id: "+ id);
			// try {
			// Thread.sleep(10000);
			// } catch (InterruptedException e1) {
			// e1.printStackTrace();
			// }

		}
	}
	
	private int[] getYMDH2(long id) {
		int[] ymdh = new int[5];
		String[] ymd = { "year", "month", "day", "hour", "DOW" };// DOW每周的星期号(0-6；星期天是0)
																	// (仅用于timestamp)
		try {
			Statement stm2 = con.createStatement();
			ResultSet rs2 = null;
			for (int i = 0; i < 5; i++) {
				// sql extract提取时间
				sql = "select extract(" + ymd[i]
						+ " from checkintime) from usertimeline where id=" + id
						+ ";";
				rs2 = stm2.executeQuery(sql);
				if (rs2.next()) {
					ymdh[i] = rs2.getInt(1);
				}
				// rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ymdh;
	}

}
